package com.red.wood.module.cdn.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.common.util.IMap;
import com.red.wood.module.cdn.dao.CdnNetInfoDao;

@Repository
public class CdnNetInfoDaoImpl implements CdnNetInfoDao {

	private static final Log log = LogFactory.getLog(CdnDataDaoImpl.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(IMap param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = filter(listPageDataStatitics(param));
		count = listCountStatistics(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public List<Map<String, Object>> getDetailInfo(String cdnNetId,String cdnId) {
		String sql = "select topDomain,cdnId,cdnNetId,timstamp from t_cdn_cdnNetInfo where cdnNetId = '"+cdnNetId+"' and cdnId = '"+cdnId+"' limit 1";
		return jdbcTemplate.queryForList(sql);
	}
	
	public List<Map<String, Object>> listPageDataStatitics(IMap param){
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		String linkDomainId = CommonUtils.valueOf(param.get("linkDomainId"));
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1")) {
			if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
				sql = getMiddleLevelSql(param);
				sql.append(getQueryConf(param));
			}else {
				sql.append("select net.province,net.cdnName,net.cdnId,net.cdnNetId,net.topDomain,net.timstamp,net.domainNum,net.ipNum,net.nodeNum,house.houseNum");
				sql.append(" from t_cdn_net_statistics net");
				sql.append(" left join (select count(1) houseNum, t.cdnNetId, t.cdnId");
				sql.append("              from (select house.cdnId,house.cdnNetId,house.houseId,house.houseProvince");
				sql.append("                      from t_cdn_cdnNet_node_house house");
				sql.append("                     where house.HouseProvince =(select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)");
				sql.append("                     group by house.cdnId, house.cdnNetId, house.houseId) t");
				sql.append("              group by t.cdnId, t.cdnNetId) house");
				sql.append(" on net.cdnId = house.cdnId and net.cdnNetId = house.cdnNetId where 1 = 1");
				sql.append(" and net.province = '"+userProvince+"'");
				sql.append(getQueryConfStatistics(param));
			}
		}else if(StringUtils.equals(userLevel, "2")) {
			if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
				sql = getMiddleLevelSql(param);
				sql.append(getQueryConf(param));
			}else {
				sql.append("select province,cdnName,cdnId,cdnNetId,topDomain,timstamp,domainNum,ipNum,nodeNum,houseNum from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province = '"+userProvince+"'");
				sql.append(getQueryConfStatistics(param));
			}
		}else if(StringUtils.equals(userLevel, "3")) {
			if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
				sql = getMiddleLevelSql(param);
				sql.append(getQueryConf(param));
			}else {
				sql.append("select province,cdnName,cdnId,cdnNetId,topDomain,timstamp,domainNum,ipNum,nodeNum,houseNum");
				sql.append(" from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province = '"+userProvince+"'");
				sql.append(getQueryConfStatistics(param));
				sql.append(" union all");
				sql.append(" select net.province,net.cdnName,net.cdnId,net.cdnNetId,net.topDomain,net.timstamp,net.domainNum,net.ipNum,net.nodeNum,net.houseNum");
				sql.append("  from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province != '"+userProvince+"'");
				sql.append(" and exists (select t1.cdnId from t_cdn_cdnNet_node_house t1 where t1.cdnId = net.cdnId and t1.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
				sql.append(getQueryConfStatistics(param));
			}
		}
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String listCountStatistics(IMap param) {
		String linkDomainId = CommonUtils.valueOf(param.get("linkDomainId"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel, "1") || StringUtils.equals(userLevel, "2")) {
			if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
				sql.append("select count(*) count from(");
				sql.append(getSqlCount(param));
				sql.append(" and cdn.province='"+userProvince+"'");
				sql.append(getQueryConf(param));
				sql.append(") t");
			}else {
				sql.append("select count(*) count from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province='"+userProvince+"'");
				sql.append(getQueryConfStatistics(param));
			}
		}else {
			if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
				sql = getMiddleLevelSql(param);
				sql.append(getQueryConf(param));
			}else {
				sql.append("select count(1) count from (");
				sql.append("select province,cdnName,cdnId,cdnNetId,topDomain,timstamp,domainNum,ipNum,nodeNum,houseNum");
				sql.append(" from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province = '"+userProvince+"'");
				sql.append(getQueryConfStatistics(param));
				sql.append(" union all");
				sql.append(" select net.province,net.cdnName,net.cdnId,net.cdnNetId,net.topDomain,net.timstamp,net.domainNum,net.ipNum,net.nodeNum,net.houseNum");
				sql.append("  from t_cdn_net_statistics net where 1 = 1");
				sql.append(" and net.province != '"+userProvince+"'");
				sql.append(" and exists (select t1.cdnId from t_cdn_cdnNet_node_house t1 where t1.cdnId = net.cdnId and t1.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
				sql.append(getQueryConfStatistics(param));
				sql.append(") t");
			}
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public List<Map<String, Object>> listPageData(IMap param){
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		
		if(StringUtils.equals(userLevel, "1")) {//初级权限
			sql = getPrimaryLevelSql(param);
			sql.append(getQueryConf(param));
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}else if(StringUtils.equals(userLevel, "2")) {//中级权限
			sql = getMiddleLevelSql(param);
			sql.append(getQueryConf(param));
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}else {//高级权限
			sql.append("select * from (");
			sql.append(getMiddleLevelSql(param));
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getPrimaryLevelSql(param));
			//sql.append(" and exists (select cdn.cdnId from t_cdn_cdnNet_node_house house where cdn.cdnId = house.cdnId and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
			sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		}
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	/**
	 * 初级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getPrimaryLevelSql(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		StringBuffer sql = new StringBuffer();
		sql.append("select net.topDomain,cdn.cdnName,net.cdnNetId,net.domainId,");
		sql.append("	   (select count(*) from t_cdn_cdnnetinfo_node node where node.cdnNetId = net.cdnNetId and node.cdnId = net.cdnId) as nodeNum,");
		sql.append("       (select count(*) from t_cdn_customer_service_domain domain where domain.domainId = net.domainId and domain.cdnId = net.cdnId ) as domainNum,");
		sql.append("       (select count(*) from t_cdn_cdnNet_node_house_ipSeg ip,t_cdn_cdnnet_node_house house where ip.cdnNetId = net.cdnNetId and ip.houseId = house.houseId and ip.cdnId = house.cdnId and net.cdnId = ip.cdnId and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)) as ipNum,");
		sql.append("       (select count(*) from t_cdn_cdnnet_node_house house where house.cdnNetId = net.cdnNetId and house.cdnId = net.cdnId and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1)) as houseNum,");
		sql.append("        cdn.province,net.cdnId");
		sql.append(" from t_cdn_cdnNetInfo net,t_cdn_cdndata cdn where net.cdnId = cdn.cdnId");
		return sql;
	}
	
	/**
	 * 中级权限
	 * @param param
	 * @return
	 */
	public StringBuffer getMiddleLevelSql(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String linkDomainId = CommonUtils.valueOf(param.get("linkDomainId"));
		String linkCdnId = CommonUtils.valueOf(param.get("linkCdnId"));
		StringBuffer sql = new StringBuffer();
		/*sql.append("select net.topDomain,cdn.cdnName,net.cdnNetId,net.domainId,");
		sql.append("	   (select count(*) from t_cdn_cdnnetinfo_node node where node.cdnNetId = net.cdnNetId and node.cdnId = net.cdnId) as nodeNum,");
		sql.append("       (select count(*) from t_cdn_customer_service_domain domain where domain.domainId = net.domainId and domain.cdnId = net.cdnId ) as domainNum,");
		sql.append("       (select count(*) from t_cdn_cdnNet_node_house_ipSeg ip,t_cdn_cdnnet_node_house house where ip.cdnNetId = net.cdnNetId and ip.houseId = house.houseId and ip.cdnId = house.cdnId and net.cdnId = ip.cdnId) as ipNum,");
		sql.append("       (select count(*) from t_cdn_cdnnet_node_house house where house.cdnNetId = net.cdnNetId and house.cdnId = net.cdnId) as houseNum,");
		sql.append("        net.modifyTime,cdn.province,net.cdnId");
		sql.append(" from t_cdn_cdnNetInfo net,t_cdn_cdndata cdn where net.cdnId = cdn.cdnId");*/
		sql.append("select cdn.cdnName,net.cdnId,net.cdnNetId,net.timstamp,net.topDomain,net.domainNum,ip.ipNum,");
		sql.append("	   (select count(1) from t_cdn_cdnnetinfo_node node where node.cdnNetId = net.cdnNetId and node.cdnId = net.cdnId) as nodeNum,");
		sql.append("       (select count(1) from t_cdn_cdnNet_node_house house where house.cdnId = net.cdnId and house.cdnNetId = net.cdnNetId group by house.cdnId,house.cdnNetId) as houseNum,");
		sql.append("        cdn.province,net.cdnId");
		sql.append(" from t_cdn_cdndata cdn");
		if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
			sql.append(" inner join (select net.cdnId,net.cdnNetId,net.topDomain,max(net.timstamp) as timstamp,count(*) as domainNum from t_cdn_cdnNetInfo net where net.cdnId = '"+linkCdnId+"' and net.domainId='"+linkDomainId+"' group by net.cdnId,net.cdnNetId,net.topDomain) net");
		}else {
			sql.append(" inner join (select net.cdnId,net.cdnNetId,net.topDomain,max(net.timstamp) as timstamp,count(*) as domainNum from t_cdn_cdnNetInfo net group by net.cdnId,net.cdnNetId,net.topDomain) net");
		}
		sql.append(" on cdn.cdnId = net.cdnId");
		sql.append(" and cdn.province = '"+userProvince+"'");
		sql.append(" left join (select ip.cdnId,ip.cdnNetId,count(1) ipNum from (select ip.cdnId,ip.cdnNetId,ip.startIp,ip.endIp from t_cdn_cdnNet_node_house_ipSeg ip group by ip.cdnId,ip.cdnNetId,ip.startIp,ip.endIp) ip group by ip.cdnId,ip.cdnNetId) ip");
		sql.append(" on ip.cdnId = net.cdnId and ip.cdnNetId = net.cdnNetId");
		sql.append(" where 1 = 1 ");
		return sql;
	}
	
	public StringBuffer getSqlCount(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String linkDomainId = CommonUtils.valueOf(param.get("linkDomainId"));
		String linkCdnId = CommonUtils.valueOf(param.get("linkCdnId"));
		StringBuffer sql = new StringBuffer();
		sql.append("select cdn.cdnName,net.cdnId,net.cdnNetId,net.topDomain,cdn.province");
		sql.append(" from t_cdn_cdndata cdn");
		if(StringUtils.isNotBlank(linkDomainId)) {//域名页面下钻过来
			sql.append(" inner join (select net.cdnId,net.cdnNetId,net.topDomain,count(*) as domainNum from t_cdn_cdnNetInfo net where net.cdnId='"+linkCdnId+"' and net.domainId='"+linkDomainId+"' group by net.cdnId,net.cdnNetId,net.topDomain) net");
		}else {
			sql.append(" inner join (select net.cdnId,net.cdnNetId,net.topDomain,count(*) as domainNum from t_cdn_cdnNetInfo net group by net.cdnId,net.cdnNetId,net.topDomain) net");
		}
		sql.append(" on cdn.cdnId = net.cdnId");
		sql.append(" and cdn.province = '"+userProvince+"'");
		/*sql.append(" left join (select ip.cdnId,ip.cdnNetId,count(1) ipNum from (select ip.cdnId,ip.cdnNetId,ip.startIp,ip.endIp from t_cdn_cdnNet_node_house_ipSeg ip group by ip.cdnId,ip.cdnNetId,ip.startIp,ip.endIp) ip group by ip.cdnId,ip.cdnNetId) ip");
		sql.append(" on ip.cdnId = net.cdnId and ip.cdnNetId = net.cdnNetId");*/
		sql.append(" where 1 = 1");
		return sql;
	}
	
	public StringBuffer getSqlCount() {
		StringBuffer sql = new StringBuffer();
		sql.append("select net.topDomain,cdn.cdnName,net.cdnNetId,cdn.province,net.cdnId");
		sql.append(" from t_cdn_cdnNetInfo net,t_cdn_cdndata cdn where net.cdnId = cdn.cdnId");
		return sql;
	}
	
	public String listCount(IMap param) {
		StringBuffer sql = new StringBuffer();
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		if(StringUtils.equals(userLevel, "3")) {
			sql.append("select count(*) count from (");
			sql.append(getSqlCount());
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSqlCount());
			//sql.append(" and exists (select cdn.cdnId from t_cdn_cdnNet_node_house house where cdn.cdnId = house.cdnId and house.HouseProvince = (select provinceid from provinces t where t.province = '"+userProvince+"' limit 1))");
			sql.append(" and cdn.province !='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}else {
			sql.append("select count(*) count from(");
			sql.append(getSqlCount(param));
			sql.append(" and cdn.province='"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") t");
		}
		
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public StringBuffer getQueryConfStatistics(IMap param) {
		String topDomain = CommonUtils.valueOf(param.get("topDomain"));
		String cdnName = CommonUtils.valueOf(param.get("cdnName"));
		String cdnId = CommonUtils.valueOf(param.get("cdnId"));
		String linkCdnId = CommonUtils.valueOf(param.get("linkCdnId"));
		String linkDomainId = CommonUtils.valueOf(param.get("linkDomainId"));
		StringBuffer queryConf = new StringBuffer();
		
		if(StringUtils.isNotBlank(linkDomainId)) {
			queryConf.append(" and net.domainId = '"+linkDomainId+"'");
		}
		if(StringUtils.isNotBlank(topDomain)) {
			queryConf.append(" and net.topDomain like '%"+topDomain+"%'");
		}
		if(StringUtils.isNotBlank(cdnName)) {
			queryConf.append(" and net.cdnName like '%"+cdnName+"%'");
		}
		if(StringUtils.isNotBlank(cdnId)) {
			queryConf.append(" and net.cdnId like '%"+cdnId+"%'");
		}
		if(StringUtils.isNotBlank(linkCdnId)) {
			queryConf.append(" and net.cdnId='"+linkCdnId+"'");
		}
		return queryConf;
	}
	
	public StringBuffer getQueryConf(IMap param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		String topDomain = CommonUtils.valueOf(param.get("topDomain"));
		String cdnName = CommonUtils.valueOf(param.get("cdnName"));
		String cdnId = CommonUtils.valueOf(param.get("cdnId"));
		String modifyTimeStart = CommonUtils.valueOf(param.get("modifyTimeStart"));
		String modifyTimeEnd = CommonUtils.valueOf(param.get("modifyTimeEnd"));
		String linkCdnId = CommonUtils.valueOf(param.get("linkCdnId"));
		StringBuffer queryConf = new StringBuffer();
		
		/*if(!StringUtils.equals(userLevel,"3")) {
			if(StringUtils.isNotBlank(userProvince)) {
				queryConf.append(" and cdn.province='"+userProvince+"'");
			}
		}*/
		if(StringUtils.isNotBlank(topDomain)) {
			queryConf.append(" and net.topDomain like '%"+topDomain+"%'");
		}
		if(StringUtils.isNotBlank(cdnName)) {
			queryConf.append(" and cdn.cdnName like '%"+cdnName+"%'");
		}
		if(StringUtils.isNotBlank(cdnId)) {
			queryConf.append(" and net.cdnId like '%"+cdnId+"%'");
		}
		if(StringUtils.isNotBlank(linkCdnId)) {
			queryConf.append(" and net.cdnId='"+linkCdnId+"'");
		}
		if(StringUtils.isNotBlank(modifyTimeStart)) {
			queryConf.append(" and net.modifyTime >='"+modifyTimeStart+"'");
		}
		if(StringUtils.isNotBlank(modifyTimeEnd)) {
			queryConf.append(" and net.modifyTime <='"+modifyTimeEnd+"'");
		}
		return queryConf;
	}
	
	public List<Map<String, Object>> filter(List<Map<String, Object>> list){
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				map.put("domainNum",CommonUtils.filteNull(map.get("domainNum")));
				map.put("ipNum",CommonUtils.filteNull(map.get("ipNum")));
				map.put("houseNum",CommonUtils.filteNull(map.get("houseNum")));
				map.put("nodeNum",CommonUtils.filteNull(map.get("nodeNum")));
			}
		}
		return list;
	}
}
